////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   MENU_OBJECT_LABEL
// AUTHOR           :   Victor Zheng 
// DATE CREATE      :   2005/12/21
// DESCRIPTION      :   a DAO file for the function, MENU_OBJECT_LABEL
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/12/21   Victor Zheng    1.0                 source is generated.
// 2006/04/26   Ric Yik         1.0                 BugZilla #5510
// 2006/04/27   Ric Yik         1.0                 BugZilla #5510
//
////////////////////////////////////////////////////////////////////////////////
 
package com.erp.app.admin.menuObjectLabel.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.erp.app.admin.menuObjectLabel.criteria.MenuObjectLabelSrchCriteria;
import com.erp.app.admin.menuObjectLabel.pk.MenuObjectLabelPk;
import com.erp.app.admin.menuObjectLabel.vo.MenuObjectLabelVo;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.util.logger.Logger;
 
/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor Zheng 
* @version 1.0     2005/12/21
* @since           FrameWork 1.0
*
*/
public class MenuObjectLabelDao extends SimplePageEditStyleDao {

    public void setMaxRows(Statement stmt) throws Exception {
        stmt.setMaxRows(0); // Return all rows
    }

    /**
    * Returns the SQL select statement.
    * @param         obj     the searching criteria
    * @return        a string containing sql query
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlQuery(SimpleBaseSrchCriteria obj) {
 
        MenuObjectLabelSrchCriteria criteria = (MenuObjectLabelSrchCriteria) obj;
        String whereClause = toWhereClause(criteria);
        String groupByClause = "";
        String orderByClause = "ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE";
 
        String sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
            + "OBJECT_NAME, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, UPDATE_USER, "
            + "UPDATE_DATE, OBJECT_TYPE, TEXT_MAX_LEN, TEXT_SIZE, TA_COL_SIZE, "
            + "TA_ROW_SIZE "
            + "FROM T_MENU_OBJECT_LABEL "
            + whereClause
            + groupByClause
            + orderByClause;
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Returns the where clause of a SQL select statement.
    * @param         obj     the searching criteria
    * @return        a string containing the where clause
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String toWhereClause(MenuObjectLabelSrchCriteria crit) {
 
        MenuObjectLabelSrchCriteria criteria = (MenuObjectLabelSrchCriteria) crit;
        StringBuffer whereClause = new StringBuffer();
 
        whereClause.append(" WHERE COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
        
        if (criteria.getSrchGroupIdFr() != null) {
            whereClause.append(" AND GROUP_ID >= '"
                + criteria.getSrchGroupIdFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchGroupIdTo() != null) {
            whereClause.append(" AND GROUP_ID <= '"
                + criteria.getSrchGroupIdTo().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuIdFr() != null) {
            whereClause.append(" AND MENU_ID >= '"
                + criteria.getSrchMenuIdFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuIdTo() != null) {
            whereClause.append(" AND MENU_ID <= '"
                + criteria.getSrchMenuIdTo().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchObjectIdFr() != null) {
            whereClause.append(" AND OBJECT_ID >= '"
                + criteria.getSrchObjectIdFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchObjectIdTo() != null) {
            whereClause.append(" AND OBJECT_ID <= '"
                + criteria.getSrchObjectIdTo().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchObjectTypeFr() != null) {
            whereClause.append(" AND OBJECT_TYPE >= '"
                + criteria.getSrchObjectTypeFr().toLowerCase().trim() + "' ");
        }
        if (criteria.getSrchObjectTypeTo() != null) {
            whereClause.append(" AND OBJECT_TYPE <= '"
                + criteria.getSrchObjectTypeTo().toLowerCase().trim() + "' ");
        }        
        if (criteria.getSrchLangCodeFr() != null) {
            whereClause.append(" AND LANG_CODE >= '"
                + criteria.getSrchLangCodeFr().trim() + "' ");
        }
        if (criteria.getSrchLangCodeTo() != null) {
            whereClause.append(" AND LANG_CODE <= '"
                + criteria.getSrchLangCodeTo().trim() + "' ");
        }
        if (criteria.getSrchObjectNameFr() != null) {
            whereClause.append(" AND OBJECT_NAME >= '"
                + criteria.getSrchObjectNameFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchObjectNameTo() != null) {
            whereClause.append(" AND OBJECT_NAME <= '"
                + criteria.getSrchObjectNameTo().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchObjectWebLabelFr() != null) {
            whereClause.append(" AND OBJECT_WEB_LABEL >= '"
                + criteria.getSrchObjectWebLabelFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchObjectWebLabelTo() != null) {
            whereClause.append(" AND OBJECT_WEB_LABEL <= '"
                + criteria.getSrchObjectWebLabelTo().toUpperCase().trim() + "' ");
        }
        return whereClause.toString();
    }
 
    /**
    * Returns an array list of child VOs by the parent
    * @param         parent   the incoming parent VO
    * @param         conn     the database connection
    * @return        an array list of child VOs
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
        throws FindRecordException {
 
        return null;
    }
 
    /**
    * Constructs a string of SQL insert statement
    * @return        a string of SQL insert statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlInsert() {
 
        String sql = "INSERT INTO T_MENU_OBJECT_LABEL ("
            + "COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
            + "OBJECT_NAME, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, UPDATE_USER, "
            + "UPDATE_DATE, OBJECT_TYPE, TEXT_MAX_LEN, TEXT_SIZE, TA_COL_SIZE, "
            + "TA_ROW_SIZE) "
            + "VALUES ("
            + "?, ?, ?, ?, ?, "
            + "?, ?, ?, SYSDATE, ?, "
            + "?, ?, ?, ?, ?, "
            + "?)";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL insert or update statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
        throws SQLException {
 
        MenuObjectLabelVo vo = (MenuObjectLabelVo) obj;
        int i = 1;
 
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getGroupId());
        pStmt.setString(i++, vo.getMenuId());
        pStmt.setString(i++, vo.getObjectId());
        pStmt.setString(i++, vo.getLangCode());
        setNullableString(pStmt, i++, vo.getObjectName());
        setNullableString(pStmt, i++, vo.getObjectWebLabel());
        setNullableString(pStmt, i++, vo.getCreateUser());
 
        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getCreateDate());
 
        setNullableString(pStmt, i++, vo.getUpdateUser());
 
        if (ProjectConstants.DB_OP_INSERT.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        setNullableString(pStmt, i++, vo.getObjectType());
        setNullableBigInteger(pStmt, i++, vo.getTextMaxLen());
        setNullableBigInteger(pStmt, i++, vo.getTextSize());
        setNullableBigInteger(pStmt, i++, vo.getTaColSize());
        setNullableBigInteger(pStmt, i++, vo.getTaRowSize());
 
        if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setString(i++, vo.getGroupId());
            pStmt.setString(i++, vo.getMenuId());
            pStmt.setString(i++, vo.getObjectId());
            pStmt.setString(i++, vo.getLangCode());
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        }
    }
 
    /**
    * Sets the values for a SQL insert statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
    }
 
    /**
    * Constructs a string of SQL update statement
    * @return        a string of SQL update statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlUpdate() {
 
        String sql = "UPDATE T_MENU_OBJECT_LABEL SET COMPANY_CODE=?, GROUP_ID=?, MENU_ID=?, OBJECT_ID=?, "
            + "LANG_CODE=?, OBJECT_NAME=?, OBJECT_WEB_LABEL=?, CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, "
            + "UPDATE_DATE=SYSDATE, OBJECT_TYPE=?, TEXT_MAX_LEN=?, TEXT_SIZE=?, TA_COL_SIZE=?, "
            + "TA_ROW_SIZE=? "
            + "WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND OBJECT_ID=? AND "
            + "LANG_CODE=? "
            + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL update statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForModify(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
    }
 
    /**
    * Constructs a string of SQL delete statement
    * @return        a string of SQL update statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlDelete() {
        String sql = "DELETE FROM T_MENU_OBJECT_LABEL WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND OBJECT_ID=? AND "
            + "LANG_CODE=? ";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL delete statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        MenuObjectLabelVo vo = (MenuObjectLabelVo) obj;
        int i = 1;
 
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getGroupId());
        pStmt.setString(i++, vo.getMenuId());
        pStmt.setString(i++, vo.getObjectId());
        pStmt.setString(i++, vo.getLangCode());
    }
 
    /**
    * Constructs a string of SQL select statement
    * @return        a string of SQL select statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlFind() {
 
        String sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
            + "OBJECT_NAME, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, UPDATE_USER, "
            + "UPDATE_DATE, OBJECT_TYPE, TEXT_MAX_LEN, TEXT_SIZE, TA_COL_SIZE, "
            + "TA_ROW_SIZE "
            + "FROM T_MENU_OBJECT_LABEL "
            + "WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND OBJECT_ID=? AND "
            + "LANG_CODE=? "
            + "ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL select statement for finding by primary key.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming PK
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForFind(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        MenuObjectLabelPk pk = (MenuObjectLabelPk) obj;
        int i = 1;
 
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
        pStmt.setString(i++, pk.getObjectId());
        pStmt.setString(i++, pk.getLangCode());
    }
 
    /**
    * Constructs a string of SQL select statement
    * @return        a string of SQL select statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlList() {
 
        return null;
    }
 
    /**
    * Sets the values for a SQL select statement for finding child tables.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming parent PK
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForList(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
    }
 
    /**
    * Sets the values of a result set to a vo
    * @param         rs   the incoming result set
    * @return        a vo containing the result set values
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
 
        MenuObjectLabelVo vo = new MenuObjectLabelVo();
 
        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setGroupId(rs.getString("GROUP_ID"));
        vo.setMenuId(rs.getString("MENU_ID"));
        vo.setObjectId(rs.getString("OBJECT_ID"));
        vo.setLangCode(rs.getString("LANG_CODE"));
        vo.setObjectName(rs.getString("OBJECT_NAME"));
        vo.setObjectWebLabel(rs.getString("OBJECT_WEB_LABEL"));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        vo.setObjectType(rs.getString("OBJECT_TYPE"));
        vo.setTextMaxLen(CustomBigInteger.customize(rs.getBigDecimal("TEXT_MAX_LEN")));
        vo.setTextSize(CustomBigInteger.customize(rs.getBigDecimal("TEXT_SIZE")));
        vo.setTaColSize(CustomBigInteger.customize(rs.getBigDecimal("TA_COL_SIZE")));
        vo.setTaRowSize(CustomBigInteger.customize(rs.getBigDecimal("TA_ROW_SIZE")));
 
        return vo;
    }
 
}
